﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web.UI.WebControls;

namespace com.orange.examination
{
    public class BaseClass 
    {
        private static readonly string connString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

        public static MySqlConnection DBCon()//建立连接数据库的公共方法
        {
            return new MySqlConnection("server=localhost;user id=root;password=123456;database=examination; pooling=true;");
        }
        //*******************************************************************************************************************************


        public static MySqlDataAdapter SelectData(string strsql)//建立一个执行查询sql语句的方法
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(strsql, conn);
            conn.Close();
            return mySqlDataAdapter;
        }

        public static MySqlDataReader SelectReader(string strsql)//建立一个执行查询sql语句的方法
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(strsql, conn);
            MySqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            conn.Close();
            return sdr;
        }




        public static void BindDG(GridView dg, string id, string strSql, string Tname)//建立绑定GridView控件的方法
        {
            MySqlConnection conn = DBCon();
            MySqlDataAdapter sda = new MySqlDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds, Tname);
            dg.DataSource = ds.Tables[Tname];
            dg.DataKeyNames = new string[] { id };
            dg.DataBind();
        }
        //*******************************************************************************************************************************


        public static int OperateData(string strsql)//建立一个执行 增、删、改、以及查询数据量的 sql语句的方法
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(strsql, conn);
            int i = 0;
            try
            {
                 i = cmd.ExecuteNonQuery();
            }
            catch (Exception) { }
            
            conn.Close();
            return i;
        }
        //============================================================================================
 
      
        //*******************************************************************************************************************************
        public static bool CheckStudent (string studentNum,string studentPwd) //检查是否是学生登入
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("select count(*) from tb_Student where StudentNum='" + studentNum + "'and StudentPwd='" + studentPwd + "'",conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        //============================================================================================
        public static bool CheckTeacher(string teacherNum, string teacherPwd)//检查是否是教师登入
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("select count(*) from tb_Teacher where TeacherNum='" + teacherNum + "'and TeacherPwd='" + teacherPwd + "'", conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        //============================================================================================
        public static bool CheckAdmin(string adminNum, string adminPwd)//检查是否是管理员登入
        {
            MySqlConnection conn = DBCon();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("select count(*) from tb_Admin where AdminNum='" + adminNum + "'and AdminPwd='" + adminPwd + "'", conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
//以下是 柒柒 的数据库操作相关代码

        /// <summary>
        /// 执行增删改操作的方法
        /// </summary>
        /// <param name="sql">增删改的SQL语句  必填</param>
        /// <param name="commandParams">参数数组  选填</param>
        /// <returns></returns>
        public static int ExecNonQuery(string sql,
            params MySqlParameter[] commandParams)
        {
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PreParedCommand(sql, commandParams, conn, cmd);

                return cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 执行查询 返回第一行第一列的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParams"></param>
        /// <returns>第一行第一列的数据</returns>
        public static object ExecScalar(string sql,
           params MySqlParameter[] commandParams)
        {
            //自动释放资源
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PreParedCommand(sql, commandParams, conn, cmd);

                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行查询 断开式
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParams"></param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecDataSet(string sql, params MySqlParameter[] commandParams)
        {
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PreParedCommand(sql, commandParams, conn, cmd);

                //创建适配器对象
                using (MySqlDataAdapter dap = new MySqlDataAdapter())
                {
                    dap.SelectCommand = cmd;//适配器对象关联command对象
                    DataSet ds = new DataSet();
                    dap.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 设置command对象属性
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParams"></param>
        /// <param name="conn"></param>
        /// <param name="cmd"></param>
        private static void PreParedCommand(string sql, MySqlParameter[] commandParams, MySqlConnection conn, MySqlCommand cmd)
        {
            //判断数据库连接对象若不是打开状态则打开
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.CommandText = sql;//设置执行的SQL语句
            cmd.Connection = conn;//关联数据库连接对象

            if (commandParams != null)
            {
                foreach (MySqlParameter sp in commandParams)
                {
                    cmd.Parameters.Add(sp);//遍历参数赋给command对象参数集合接收
                }
            }
        }
        //weisong
        public static MySqlDataReader ExecDataReader(string sql, params MySqlParameter[] commandParams)
        {
            MySqlConnection conn = DBCon();
            MySqlCommand cmd = new MySqlCommand();
            PreParedCommand(sql, commandParams, conn, cmd);

            //CommandBehavior.CloseConnection:若dataReader对象关闭则自动关闭connection对象
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}